﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class sql
    {
        public static SqlConnection LayKetNoi()
        {
            SqlConnection cnn = new SqlConnection(@"Data Source=TRANTHANH-PC\SQLEXPRESS;Initial Catalog=BanSach;Integrated Security=True");
            return cnn;
        }
    }
    public class Ban_DAO
    {
        //load danh sach HD:
        public static DataTable LoadDSHDBan()
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("SelectAll_HDBan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //load danh sach NhanVien Ban Hang:
        public static DataTable LoadDSNVBanHang()
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("NhanVienBanHang", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //load Tinh hinh ban sach:
        public static DataTable LoadBanSach()
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("SachBanChay", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //load Tong So Tien cua hoa don:
        public static DataTable LoadDSTongtienHD()
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("TongTienHD", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }

        //Them HD Moi:
        public static void ThemHDBan(Ban_DTO hd)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("insert_HDBan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHD", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@ngayban", SqlDbType.Date);
            cmd.Parameters.Add("@Giaban", SqlDbType.Float);
            cmd.Parameters.Add("@soluong", SqlDbType.Int);
            cmd.Parameters.Add("@ChietKhau", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@MaNV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@MaKH", SqlDbType.NChar, 10);
            //gan:
            cmd.Parameters["@maHD"].Value = hd.MaHD;
            cmd.Parameters["@masach"].Value = hd.MaSach;
            cmd.Parameters["@ngayban"].Value = hd.NgayBan;
            cmd.Parameters["@Giaban"].Value = hd.GiaBan;
            cmd.Parameters["@soluong"].Value = hd.SoLuong;
            cmd.Parameters["@ChietKhau"].Value = hd.ChietKhau;
            cmd.Parameters["@GhiChu"].Value = hd.GhiChu;
            cmd.Parameters["@MaNV"].Value = hd.MaNV;
            cmd.Parameters["@MaKH"].Value = hd.MaKH;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }

        //Sua Hd:
        public static void SuaHD(Ban_DTO sua)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("Update_HDBan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHD", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@ngayban", SqlDbType.Date);
            cmd.Parameters.Add("@Giaban", SqlDbType.Float);
            cmd.Parameters.Add("@soluong", SqlDbType.Int);
            cmd.Parameters.Add("@ChietKhau", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@MaNV", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@MaKH", SqlDbType.NChar, 10);
            //gan:
            cmd.Parameters["@maHD"].Value = sua.MaHD;
            cmd.Parameters["@masach"].Value = sua.MaSach;
            cmd.Parameters["@ngayban"].Value = sua.NgayBan;
            cmd.Parameters["@Giaban"].Value = sua.GiaBan;
            cmd.Parameters["@soluong"].Value = sua.SoLuong;
            cmd.Parameters["@ChietKhau"].Value = sua.ChietKhau;
            cmd.Parameters["@GhiChu"].Value = sua.GhiChu;
            cmd.Parameters["@MaNV"].Value = sua.MaNV;
            cmd.Parameters["@MaKH"].Value = sua.MaKH;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Xoa HD:
        public static void XoaHD(string mahd)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("Delete_HDBan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaHDBan", SqlDbType.NChar, 10);
            cmd.Parameters["@MaHDBan"].Value = mahd;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Tim HD Nhap Theo MaHDBan:
        public static DataTable TimMaHDBan(string mahd)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("TimtheoMaHDBan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = mahd;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim Theo Ma Sach:
        public static DataTable TimMaSach(string masach)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("TimtheoMaCuaSach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = masach;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim theo ma NV:
        public static DataTable TimMaNV(string manv)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("TimtheoMaNhanVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = manv;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim MaNXB:
        public static DataTable TimMaKH(string makh)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("TimtheoMaKH", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = makh;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Thong Ke Theo Ma HD:
        public static DataTable TongMaHD(string mahd)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("tienbantheomaHD", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ma", SqlDbType.NChar, 10);
            cmd.Parameters["@ma"].Value = mahd;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Thong Ke Theo ngay :
        public static DataTable TongTienTheoNgay(DateTime ngay)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("tienbantheongay", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ngay", SqlDbType.Date);
            cmd.Parameters["@ngay"].Value = ngay;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Thong Ke Theo NhanVien :
        public static DataTable TongTienTheoNhanVien(string ten)
        {
            SqlConnection cnn = sql.LayKetNoi();
            SqlCommand cmd = new SqlCommand("tienbantheonhanvien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ten", SqlDbType.NVarChar, 50);
            cmd.Parameters["@ten"].Value = ten;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        

    }
}
